<?php /* 	These code was generated using phpCIGen v 0.1.a (21/04/2009)
	#zaqi 		zaqi.smart@gmail.com,http://zenzaqi.blogspot.com,
    #songbee	mukhlisona@gmail.com
	#CV. Trust Solution, jl. Saronojiwo 19 Surabaya, http://www.ts.co.id

	+ Module  		: t_bbmquality Model
	+ Description	: For record model process back-end
	+ Filename 		: M_t_bbmquality_summary.php
 	+ creator 		: 
 	+ Created on 31/May/2011 09:41:28

*/

class M_t_bill_lap extends CI_Model{

	//constructor
	function M_laporan_cat_pers_bbm() {
		parent::__construct();
	}
	
	function get_list($tgl_awal, $tgl_akhir, $warehouse){
		$jumlah_hari=0;
		$sql_tgl="SELECT
			to_date('".$tgl_akhir."', 'YYYY-MM-DD')- to_date('".$tgl_awal."', 'YYYY-MM-DD')AS total_hari
			from s_spbu";
			
		$result = $this->db->query($sql_tgl);
		$nbrows = $result->num_rows();
		if($nbrows>0){
			$row=$result->row();
			$jumlah_hari=$row->total_hari;
		}
		//$this->firephp->log("jumlah hari :".$jumlah_hari);
		
		/*$sql = "SELECT
				vu_masuk.*, vu_stok.stok_awal,
				vu_keluar.pengeluaran_dispenser,
				vu_masuk.bbmrec_volume_b + vu_masuk.bbmrec_volume_actual - vu_keluar.pengeluaran_dispenser AS stok_akhir_teoritis,
				vu_stok.stok_akhir AS stok_akhir_aktual,
				vu_masuk.bbmrec_volume_b + vu_masuk.bbmrec_volume_actual - vu_keluar.pengeluaran_dispenser - vu_stok.stok_akhir AS stok_akhir_selisih
			FROM
				(
					SELECT
						vu_genshift_bydate.shift_date,
						vu_genshift_bydate.shift_id,
						vu_receive.bbmrec_nopol,
						vu_receive.bbmrec_do,
						COALESCE(
							vu_receive.bbmrec_volume_b,
							0
						)AS bbmrec_volume_b,
						COALESCE(vu_receive.bbmrec_volume, 0)AS bbmrec_volume,
						COALESCE(
							vu_receive.bbmrec_volume_actual,
							0
						)AS bbmrec_volume_actual,
						COALESCE(
							vu_receive.bbmrec_volume_diff,
							0
						)AS bbmrec_volume_diff
					FROM
						(
							SELECT
								generate_series(0, ".$jumlah_hari.")+ DATE '".$tgl_awal."' AS shift_date,
								shift_id,
								shift_name
							FROM
								m_shift
						)AS vu_genshift_bydate
					LEFT JOIN(
						SELECT
							vu_penerimaan_detail.bbmrec_shift_date,
							vu_penerimaan_detail.m_shift_id,
							array_to_string(
								ARRAY_AGG(
									vu_penerimaan_detail.bbmrec_nopol
								),
								','
							)AS bbmrec_nopol,
							array_to_string(
								ARRAY_AGG(
									vu_penerimaan_detail.bbmrec_do
								),
								','
							)AS bbmrec_do,
							SUM(
								vu_penerimaan_detail.bbmrec_volume_b
							)AS bbmrec_volume_b,
							SUM(
								vu_penerimaan_detail.bbmrec_volume
							)AS bbmrec_volume,
							SUM(
								vu_penerimaan_detail.bbmrec_volume_actual
							)AS bbmrec_volume_actual,
							SUM(
								vu_penerimaan_detail.bbmrec_volume_diff
							)AS bbmrec_volume_diff
						FROM
							(
								SELECT
									t_bbmrec.bbmrec_shift_date,
									t_bbmrec.m_shift_id,
									t_bbmrec.bbmrec_nopol,
									t_bbmrec.bbmrec_do,
									t_bbmrec.bbmrec_volume_b,
									t_bbmrec.bbmrec_volume,
									(
										t_bbmrec.bbmrec_volume_a - t_bbmrec.bbmrec_volume_b
									)AS bbmrec_volume_actual,
									(
										(
											t_bbmrec.bbmrec_volume_a - t_bbmrec.bbmrec_volume_b
										)- t_bbmrec.bbmrec_volume
									)AS bbmrec_volume_diff
								FROM
									t_bbmrec
								WHERE
									t_bbmrec.m_warehouse_id = ".$warehouse."
							)AS vu_penerimaan_detail
						GROUP BY
							vu_penerimaan_detail.bbmrec_shift_date,
							vu_penerimaan_detail.m_shift_id
					)AS vu_receive ON(
						vu_receive.bbmrec_shift_date = vu_genshift_bydate.shift_date
						AND vu_receive.m_shift_id = vu_genshift_bydate.shift_id
					)
					ORDER BY
						vu_genshift_bydate.shift_date ASC,
						vu_genshift_bydate.shift_id ASC
				)vu_masuk
			JOIN(
				SELECT
					vu_genshift_bydate.shift_date,
					vu_genshift_bydate.shift_id,
					COALESCE(
						vu_sales.pengeluaran_dispenser,
						0
					)AS pengeluaran_dispenser
				FROM
					(
						SELECT
							generate_series(0,".$jumlah_hari.")+ DATE '".$tgl_awal."' AS shift_date,
							shift_id,
							shift_name
						FROM
							m_shift
					)AS vu_genshift_bydate
				LEFT JOIN(
					SELECT
						to_char(
							t_schedule.schedule_date,
							'YYYY-MM-DD'
						)AS shift_date,
						m_shift.shift_id,
						SUM(t_bbmsales.bbmsales_volume)AS pengeluaran_dispenser
					FROM
						t_bbmsales,
						t_schedet,
						t_schedule,
						m_shift,
						m_nozzle
					WHERE
						t_bbmsales.t_schedet_id = t_schedet.schedet_id
					AND t_schedet.t_schedule_id = t_schedule.schedule_id
					AND t_schedule.m_shift_id = m_shift.shift_id
					AND t_bbmsales.m_nozzle_id = m_nozzle.nozzle_id
					AND m_nozzle.m_warehouse_id = ".$warehouse."
					GROUP BY
						to_char(
							t_schedule.schedule_date,
							'YYYY-MM-DD'
						),
						m_shift.shift_id
				)AS vu_sales ON(
					to_date(
						vu_sales.shift_date,
						'YYYY-MM-DD'
					)= vu_genshift_bydate.shift_date
					AND vu_sales.shift_id = vu_genshift_bydate.shift_id
				)
				ORDER BY
					vu_genshift_bydate.shift_date ASC,
					vu_genshift_bydate.shift_id ASC
			)vu_keluar ON vu_masuk.shift_date = vu_keluar.shift_date
			AND vu_masuk.shift_id = vu_keluar.shift_id
			JOIN(
				SELECT
					vu_genshift_bydate.shift_date,
					vu_genshift_bydate.shift_id,
					COALESCE(vu_stok.stok_awal, 0)AS stok_awal,
					COALESCE(vu_stok.stok_akhir, 0)AS stok_akhir
				FROM
					(
						SELECT
							generate_series(0, ".$jumlah_hari.")+ DATE '".$tgl_awal."' AS shift_date,
							shift_id,
							shift_name
						FROM
							m_shift
					)AS vu_genshift_bydate
				LEFT JOIN(
					SELECT
						to_char(
							t_bbmstock.bbmstock_date,
							'YYYY-MM-DD'
						)AS shift_date,
						t_bbmstock.shift_id,
						t_bbmstock.bbmstock_volumea AS stok_awal,
						t_bbmstock.bbmstock_volume AS stok_akhir
					FROM
						t_bbmstock
					JOIN(
						SELECT
							MAX(t_bbmstock.bbmstock_id)AS max_id,
							shift_id,
							t_bbmstock.bbmstock_date
						FROM
							t_bbmstock
						WHERE
							t_bbmstock.warehouse_id = ".$warehouse."
						GROUP BY
							t_bbmstock.bbmstock_date,
							shift_id
						ORDER BY
							t_bbmstock.bbmstock_date ASC,
							shift_id ASC
					)AS max_stok ON t_bbmstock.bbmstock_id = max_stok.max_id
					ORDER BY
						to_char(
							t_bbmstock.bbmstock_date,
							'YYYY-MM-DD'
						)ASC,
						t_bbmstock.shift_id ASC
				)AS vu_stok ON(
					to_date(
						vu_stok.shift_date,
						'YYYY-MM-DD'
					)= vu_genshift_bydate.shift_date
					AND vu_stok.shift_id = vu_genshift_bydate.shift_id
				)
				ORDER BY
					vu_genshift_bydate.shift_date ASC,
					vu_genshift_bydate.shift_id ASC
			)vu_stok ON vu_masuk.shift_date = vu_stok.shift_date
			AND vu_masuk.shift_id = vu_stok.shift_id
			AND vu_keluar.shift_date = vu_stok.shift_date
			AND vu_keluar.shift_id = vu_stok.shift_id";*/
		
		$sql = "SELECT vu_genshift_bydate.*,
				COALESCE(t_bbmstock.bbmstock_volumea,0) AS stok_awal,
				t_bbmrec.bbmrec_nopol,
				t_bbmrec.bbmrec_do,
				COALESCE(t_bbmrec.bbmrec_volume_a,0) AS bbmrec_volume_a,
				COALESCE(t_bbmrec.bbmrec_volume,0) AS bbmrec_volume,
				((COALESCE(t_bbmrec.bbmrec_volume_b,0) - COALESCE(t_bbmrec.bbmrec_volume_a,0)) + (COALESCE(t_bbmrec.bbmrec_totalizer_b,0) - COALESCE(t_bbmrec.bbmrec_totalizer_a,0))) AS bbmrec_volume_actual,
				(((COALESCE(t_bbmrec.bbmrec_volume_b,0) - COALESCE(t_bbmrec.bbmrec_volume_a,0)) + (COALESCE(t_bbmrec.bbmrec_totalizer_b,0) - COALESCE(t_bbmrec.bbmrec_totalizer_a,0))) - COALESCE(t_bbmrec.bbmrec_volume,0)) AS bbmrec_volume_diff,
				COALESCE(vu_bbmsales.bbmsales_volume,0) AS pengeluaran_dispenser,
				COALESCE((t_bbmstock.bbmstock_volumea + ((COALESCE(t_bbmrec.bbmrec_volume_b,0) - COALESCE(t_bbmrec.bbmrec_volume_a,0)) + (COALESCE(t_bbmrec.bbmrec_totalizer_b,0) - COALESCE(t_bbmrec.bbmrec_totalizer_a,0))) - COALESCE(vu_bbmsales.bbmsales_volume,0)),0) AS stok_akhir_teoritis,
				COALESCE(t_bbmstock.bbmstock_volume,0) AS stok_akhir_aktual,
				(t_bbmstock.bbmstock_volume - COALESCE((t_bbmstock.bbmstock_volumea + ((COALESCE(t_bbmrec.bbmrec_volume_b,0) - COALESCE(t_bbmrec.bbmrec_volume_a,0)) + (COALESCE(t_bbmrec.bbmrec_totalizer_b,0) - COALESCE(t_bbmrec.bbmrec_totalizer_a,0))) - COALESCE(vu_bbmsales.bbmsales_volume,0)),0)) AS stok_akhir_selisih
			FROM
				(SELECT
					generate_series(0, ".$jumlah_hari.")+ DATE '".$tgl_awal."' AS shift_date,
					shift_id,
					shift_name
				FROM
					m_shift) AS vu_genshift_bydate
			LEFT JOIN t_bbmstock ON((DATE (t_bbmstock.bbmstock_date))=vu_genshift_bydate.shift_date AND t_bbmstock.shift_id=vu_genshift_bydate.shift_id)
			LEFT JOIN t_bbmrec ON(t_bbmrec.bbmrec_shift_date=vu_genshift_bydate.shift_date AND t_bbmrec.m_shift_id=vu_genshift_bydate.shift_id)
			LEFT JOIN (SELECT (DATE (t_bbmsales.bbmsales_shiftdate)) AS bbmsales_shiftdate,
					t_bbmsales.m_shift_id,
					sum(t_bbmsales.bbmsales_volume) AS bbmsales_volume
				FROM t_bbmsales 
				GROUP BY bbmsales_shiftdate, t_bbmsales.m_shift_id) AS vu_bbmsales ON(vu_bbmsales.bbmsales_shiftdate=vu_genshift_bydate.shift_date AND vu_bbmsales.m_shift_id=vu_genshift_bydate.shift_id)
			ORDER BY vu_genshift_bydate.shift_date, vu_genshift_bydate.shift_id";
		
		//$this->firephp->log($sql, 'sql');
		
		$result = $this->db->query($sql);
		$nbrows = $result->num_rows();
	
		if($nbrows>0){
			foreach($result->result() as $row){
				$arr[] = $row;
			}
			$jsonresult = json_encode($arr);
			return '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
		} else {
			return '({"total":"0", "results":""})';
		}
		//return $sql_tgl;
	}
	
	function print_list($tgl_awal, $tgl_akhir, $warehouse){
		
		$jumlah_hari=0;
		$sql_tgl="SELECT
			to_date('".$tgl_akhir."', 'YYYY-MM-DD')- to_date('".$tgl_awal."', 'YYYY-MM-DD')AS total_hari
			from s_spbu";
			
		$result = $this->db->query($sql_tgl);
		$nbrows = $result->num_rows();
		if($nbrows>0){
			$row=$result->row();
			$jumlah_hari=$row->total_hari;
		}
		
		/*$sql = "SELECT
				vu_masuk.*, vu_stok.stok_awal,
				vu_keluar.pengeluaran_dispenser,
				vu_masuk.bbmrec_volume_b + vu_masuk.bbmrec_volume_actual - vu_keluar.pengeluaran_dispenser AS stok_akhir_teoritis,
				vu_stok.stok_akhir AS stok_akhir_aktual,
				vu_masuk.bbmrec_volume_b + vu_masuk.bbmrec_volume_actual - vu_keluar.pengeluaran_dispenser - vu_stok.stok_akhir AS stok_akhir_selisih
			FROM
				(
					SELECT
						vu_genshift_bydate.shift_date,
						vu_genshift_bydate.shift_id,
						vu_receive.bbmrec_nopol,
						vu_receive.bbmrec_do,
						COALESCE(
							vu_receive.bbmrec_volume_b,
							0
						)AS bbmrec_volume_b,
						COALESCE(vu_receive.bbmrec_volume, 0)AS bbmrec_volume,
						COALESCE(
							vu_receive.bbmrec_volume_actual,
							0
						)AS bbmrec_volume_actual,
						COALESCE(
							vu_receive.bbmrec_volume_diff,
							0
						)AS bbmrec_volume_diff
					FROM
						(
							SELECT
								generate_series(0, ".$jumlah_hari.")+ DATE '".$tgl_awal."' AS shift_date,
								shift_id,
								shift_name
							FROM
								m_shift
						)AS vu_genshift_bydate
					LEFT JOIN(
						SELECT
							vu_penerimaan_detail.bbmrec_shift_date,
							vu_penerimaan_detail.m_shift_id,
							array_to_string(
								ARRAY_AGG(
									vu_penerimaan_detail.bbmrec_nopol
								),
								','
							)AS bbmrec_nopol,
							array_to_string(
								ARRAY_AGG(
									vu_penerimaan_detail.bbmrec_do
								),
								','
							)AS bbmrec_do,
							SUM(
								vu_penerimaan_detail.bbmrec_volume_b
							)AS bbmrec_volume_b,
							SUM(
								vu_penerimaan_detail.bbmrec_volume
							)AS bbmrec_volume,
							SUM(
								vu_penerimaan_detail.bbmrec_volume_actual
							)AS bbmrec_volume_actual,
							SUM(
								vu_penerimaan_detail.bbmrec_volume_diff
							)AS bbmrec_volume_diff
						FROM
							(
								SELECT
									t_bbmrec.bbmrec_shift_date,
									t_bbmrec.m_shift_id,
									t_bbmrec.bbmrec_nopol,
									t_bbmrec.bbmrec_do,
									t_bbmrec.bbmrec_volume_b,
									t_bbmrec.bbmrec_volume,
									(
										t_bbmrec.bbmrec_volume_a - t_bbmrec.bbmrec_volume_b
									)AS bbmrec_volume_actual,
									(
										(
											t_bbmrec.bbmrec_volume_a - t_bbmrec.bbmrec_volume_b
										)- t_bbmrec.bbmrec_volume
									)AS bbmrec_volume_diff
								FROM
									t_bbmrec
								WHERE
									t_bbmrec.m_warehouse_id = ".$warehouse."
							)AS vu_penerimaan_detail
						GROUP BY
							vu_penerimaan_detail.bbmrec_shift_date,
							vu_penerimaan_detail.m_shift_id
					)AS vu_receive ON(
						vu_receive.bbmrec_shift_date = vu_genshift_bydate.shift_date
						AND vu_receive.m_shift_id = vu_genshift_bydate.shift_id
					)
					ORDER BY
						vu_genshift_bydate.shift_date ASC,
						vu_genshift_bydate.shift_id ASC
				)vu_masuk
			JOIN(
				SELECT
					vu_genshift_bydate.shift_date,
					vu_genshift_bydate.shift_id,
					COALESCE(
						vu_sales.pengeluaran_dispenser,
						0
					)AS pengeluaran_dispenser
				FROM
					(
						SELECT
							generate_series(0,".$jumlah_hari.")+ DATE '".$tgl_awal."' AS shift_date,
							shift_id,
							shift_name
						FROM
							m_shift
					)AS vu_genshift_bydate
				LEFT JOIN(
					SELECT
						to_char(
							t_schedule.schedule_date,
							'YYYY-MM-DD'
						)AS shift_date,
						m_shift.shift_id,
						SUM(t_bbmsales.bbmsales_volume)AS pengeluaran_dispenser
					FROM
						t_bbmsales,
						t_schedet,
						t_schedule,
						m_shift,
						m_nozzle
					WHERE
						t_bbmsales.t_schedet_id = t_schedet.schedet_id
					AND t_schedet.t_schedule_id = t_schedule.schedule_id
					AND t_schedule.m_shift_id = m_shift.shift_id
					AND t_bbmsales.m_nozzle_id = m_nozzle.nozzle_id
					AND m_nozzle.m_warehouse_id = ".$warehouse."
					GROUP BY
						to_char(
							t_schedule.schedule_date,
							'YYYY-MM-DD'
						),
						m_shift.shift_id
				)AS vu_sales ON(
					to_date(
						vu_sales.shift_date,
						'YYYY-MM-DD'
					)= vu_genshift_bydate.shift_date
					AND vu_sales.shift_id = vu_genshift_bydate.shift_id
				)
				ORDER BY
					vu_genshift_bydate.shift_date ASC,
					vu_genshift_bydate.shift_id ASC
			)vu_keluar ON vu_masuk.shift_date = vu_keluar.shift_date
			AND vu_masuk.shift_id = vu_keluar.shift_id
			JOIN(
				SELECT
					vu_genshift_bydate.shift_date,
					vu_genshift_bydate.shift_id,
					COALESCE(vu_stok.stok_awal, 0)AS stok_awal,
					COALESCE(vu_stok.stok_awal, 0)AS stok_akhir
				FROM
					(
						SELECT
							generate_series(0, ".$jumlah_hari.")+ DATE '".$tgl_awal."' AS shift_date,
							shift_id,
							shift_name
						FROM
							m_shift
					)AS vu_genshift_bydate
				LEFT JOIN(
					SELECT
						to_char(
							t_bbmstock.bbmstock_date,
							'YYYY-MM-DD'
						)AS shift_date,
						t_bbmstock.shift_id,
						t_bbmstock.bbmstock_volumea AS stok_awal,
						t_bbmstock.bbmstock_volume AS stok_akhir
					FROM
						t_bbmstock
					JOIN(
						SELECT
							MAX(t_bbmstock.bbmstock_id)AS max_id,
							shift_id,
							t_bbmstock.bbmstock_date
						FROM
							t_bbmstock
						WHERE
							t_bbmstock.warehouse_id = ".$warehouse."
						GROUP BY
							t_bbmstock.bbmstock_date,
							shift_id
						ORDER BY
							t_bbmstock.bbmstock_date ASC,
							shift_id ASC
					)AS max_stok ON t_bbmstock.bbmstock_id = max_stok.max_id
					ORDER BY
						to_char(
							t_bbmstock.bbmstock_date,
							'YYYY-MM-DD'
						)ASC,
						t_bbmstock.shift_id ASC
				)AS vu_stok ON(
					to_date(
						vu_stok.shift_date,
						'YYYY-MM-DD'
					)= vu_genshift_bydate.shift_date
					AND vu_stok.shift_id = vu_genshift_bydate.shift_id
				)
				ORDER BY
					vu_genshift_bydate.shift_date ASC,
					vu_genshift_bydate.shift_id ASC
			)vu_stok ON vu_masuk.shift_date = vu_stok.shift_date
			AND vu_masuk.shift_id = vu_stok.shift_id
			AND vu_keluar.shift_date = vu_stok.shift_date
			AND vu_keluar.shift_id = vu_stok.shift_id";*/
		$sql = "SELECT vu_genshift_bydate.*,
				t_bbmstock.bbmstock_volumea AS stok_awal,
				t_bbmrec.bbmrec_nopol,
				t_bbmrec.bbmrec_do,
				t_bbmrec.bbmrec_volume_a,
				t_bbmrec.bbmrec_volume,
				((t_bbmrec.bbmrec_volume_b - t_bbmrec.bbmrec_volume_a) + (t_bbmrec.bbmrec_totalizer_b - t_bbmrec.bbmrec_totalizer_a)) AS bbmrec_volume_actual,
				(((t_bbmrec.bbmrec_volume_b - t_bbmrec.bbmrec_volume_a) + (t_bbmrec.bbmrec_totalizer_b - t_bbmrec.bbmrec_totalizer_a)) - t_bbmrec.bbmrec_volume) AS bbmrec_volume_diff,
				vu_bbmsales.bbmsales_volume AS pengeluaran_dispenser,
				(t_bbmstock.bbmstock_volumea + ((t_bbmrec.bbmrec_volume_b - t_bbmrec.bbmrec_volume_a) + (t_bbmrec.bbmrec_totalizer_b - t_bbmrec.bbmrec_totalizer_a)) - vu_bbmsales.bbmsales_volume) AS stok_akhir_teoritis,
				t_bbmstock.bbmstock_volume AS stok_akhir_aktual,
				(t_bbmstock.bbmstock_volume - (t_bbmstock.bbmstock_volumea + ((t_bbmrec.bbmrec_volume_b - t_bbmrec.bbmrec_volume_a) + (t_bbmrec.bbmrec_totalizer_b - t_bbmrec.bbmrec_totalizer_a)) - vu_bbmsales.bbmsales_volume)) AS stok_akhir_selisih
			FROM
				(SELECT
					generate_series(0, ".$jumlah_hari.")+ DATE '".$tgl_awal."' AS shift_date,
					shift_id,
					shift_name
				FROM
					m_shift) AS vu_genshift_bydate
			LEFT JOIN t_bbmstock ON((DATE (t_bbmstock.bbmstock_date))=vu_genshift_bydate.shift_date AND t_bbmstock.shift_id=vu_genshift_bydate.shift_id)
			LEFT JOIN t_bbmrec ON(t_bbmrec.bbmrec_shift_date=vu_genshift_bydate.shift_date AND t_bbmrec.m_shift_id=vu_genshift_bydate.shift_id)
			LEFT JOIN (SELECT (DATE (t_bbmsales.bbmsales_shiftdate)) AS bbmsales_shiftdate,
					t_bbmsales.m_shift_id,
					sum(t_bbmsales.bbmsales_volume) AS bbmsales_volume
				FROM t_bbmsales 
				GROUP BY bbmsales_shiftdate, t_bbmsales.m_shift_id) AS vu_bbmsales ON(vu_bbmsales.bbmsales_shiftdate=vu_genshift_bydate.shift_date AND vu_bbmsales.m_shift_id=vu_genshift_bydate.shift_id)
			ORDER BY vu_genshift_bydate.shift_date, vu_genshift_bydate.shift_id";
			
		$result = $this->db->query($sql);
		$nbrows = $result->num_rows();
	
		if($nbrows>0){
			return $result->result();
		} else {
			return 0;
		}
	}
	

}
?>